package br.com.lumis.saas.service.consumerclient.persistence.dao;


import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.List;

import lumis.portal.PortalException;
import lumis.portal.UnexpectedException;
import lumis.portal.authentication.SessionConfig;
import lumis.portal.cache.PortalCache;
import lumis.portal.dao.jdbc.ITransactionJdbc;
import lumis.portal.transaction.ITransaction;
import lumis.util.JdbcUtil;
import lumis.util.log.ILogger;
import lumis.util.log.LoggerFactory;
import br.com.lumis.saas.SaasRequestParameters;
import br.com.lumis.saas.service.consumerclient.model.ConsumerClient;
import br.com.lumis.saas.service.consumerclient.model.Customer;
import br.com.lumis.saas.service.consumerclient.model.Environment;


public class ConsumerClientDAO {	
	private static final String QUERY_ADD = "insert into saas_consumerClient(id, pageview, transf, storage, datefull, environmentId) values(?,?,?,?,?,?)";
	private static final String QUERY_REPORT_CONSOLIDED = "select  environmentId, name, url, status  from saas_vw_reportEnvironmentConsolidated where customerId = ?  group by environmentId";
	private static final ILogger logger = LoggerFactory.getLogger(ConsumerClientDAO.class);	
	
	
	public static PortalCache<List<Environment>> environmentClientCache = new PortalCache<List<Environment>>("br.com.lumis.saas.service.consumerclient.model.Environment");
	
	private CustomerDAO customerDAO = new CustomerDAO();
	
	/**
	 * Add ConsumerClient Object
	 * @param environment
	 * @param transaction
	 * @throws PortalException
	 */
	public void add(Environment environment,  ITransaction transaction)  throws PortalException{
		try{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();
			
			List<ConsumerClient> clients = environment.getConsumerClients();
			Collection<String> ids= getConsumerClientByDateAndEnvironment(clients.get(0).getDatefull(), environment.getId(), transaction);
			
			logger.info("===> ids size= " + ids.size() );
			if(ids.size()==0)
			{							
				logger.info("===> Adicionando ");
				
				PreparedStatement statement = connection.prepareStatement(QUERY_ADD);
				try{					
					statement.setString(1, clients.get(0).getId());
					statement.setDouble(2, clients.get(0).getPageviews());
					statement.setDouble(3, clients.get(0).getTransfer());
					statement.setDouble(4, clients.get(0).getStorage());
					statement.setDate(5, clients.get(0).getDatefull());
					statement.setString(6, environment.getId());
					statement.execute();
				}
				finally{
					statement.close();
				}				
			}	
			else{
				if(clients.get(0).getPageviews() != 0){
					updatePageView(environment, daoTransactionJdbc);		
				}else if(clients.get(0).getTransfer() != 0){
					updateTransf(environment, daoTransactionJdbc);					
				}else if(clients.get(0).getStorage() != 0){
					updateStorage(environment, daoTransactionJdbc);					
				}
				
				
			}
		}		
		catch (Exception e){
			throw new UnexpectedException(e);
		}
	}
	
	/**
	 * 
	 * @param sessionConfig
	 * @param transaction
	 * @return
	 * @throws PortalException
	 */
	public List<Environment> getReportEnvironmentConsolided(SessionConfig sessionConfig, ITransaction transaction) throws PortalException{		
		List<Environment> environments = new ArrayList<Environment>();					
		try{										
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			
			Customer customer = customerDAO.getByLumUserId(sessionConfig.getUserId(), daoTransactionJdbc);
			
			if(environmentClientCache.get("enviroment_customer_" + customer.getId()) != null){
				return environmentClientCache.get("enviroment_customer_"+customer.getId());
			}					
			Connection connection = daoTransactionJdbc.getConnection();

			Calendar cal = Calendar.getInstance();
			
			int diaPrimeiro = cal.getActualMinimum(Calendar.DAY_OF_MONTH);
			int diaUltimo = cal.get(Calendar.DAY_OF_MONTH);
			int month = cal.get(Calendar.MONTH)+1;
			int year = cal.get(Calendar.YEAR);
			
			String datestart = String.valueOf(year)+'-'+String.valueOf(month)+'-'+String.valueOf(diaPrimeiro);
			String dateend = String.valueOf(year)+'-'+String.valueOf(month)+'-'+String.valueOf(diaUltimo);					
			
			PreparedStatement statement = connection.prepareStatement(QUERY_REPORT_CONSOLIDED);
			try{								
				
				statement.setString(1, customer.getId());
				
				ResultSet resultSet = statement.executeQuery();
											
				try{
					while(resultSet.next()){
						
						Environment environment = new Environment();
						environment.setId(resultSet.getString(SaasRequestParameters.ENVIRONMENTID));
						environment.setName(resultSet.getString(SaasRequestParameters.PAGE_PARAMETER_NAME));
						environment.setUrl(resultSet.getString(SaasRequestParameters.URL));						
						environment.setStatus(resultSet.getInt(SaasRequestParameters.STATUS));
						
						ConsumerClient client = new ConsumerClient();
						
						client.setPageviews(getPageViews(environment.getId(), datestart, dateend, daoTransactionJdbc));
						client.setStorage(getStorage(environment.getId(), datestart, dateend, daoTransactionJdbc));
						client.setTransfer(getTransfer(environment.getId(), datestart, dateend, daoTransactionJdbc));
						
						
						List<ConsumerClient> customers = new ArrayList<ConsumerClient>();
						customers.add(client);
						
						environment.setConsumerClients(customers);						
						environments.add(environment);											
					}
					
					environmentClientCache.put("enviroment_customer_"+customer.getId(), environments);					
					return environments;
				}
				finally{
					resultSet.close();
				}
			}
			finally{
				statement.close();
			}						
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}			
	}	
	
	
	public Collection<String> getConsumerClientByDateAndEnvironment(Date datefull, String environmentId, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("select id from saas_consumerClient where datefull = ? and environmentId=?");
			try
			{
				statement.setDate(1, datefull);
				statement.setString(2, environmentId);
				return JdbcUtil.fillListString(statement, "id");
			}
			finally
			{
				statement.close();
			}

		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}

	private Long getPageViews(String environmentId, String datestart,
			String dateend, ITransaction transaction) throws PortalException {

		try {
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection
					.prepareStatement("select SUM(pageview) pageview from saas_consumerClient where environmentId=? AND datefull BETWEEN ?  AND ?");
			try {
				statement.setString(1, environmentId);
				statement.setString(2, datestart);
				statement.setString(3, dateend);
				ResultSet resultSet = statement.executeQuery();
				try {
					if (resultSet.next()) {

						return resultSet.getLong("pageview");
					}

					
				} finally {
					resultSet.close();
				}

			} finally {
				statement.close();
			}
			
			return Long.valueOf(0);

		} catch (PortalException e) {
			throw e;
		} catch (Exception e) {
			throw new UnexpectedException(e);
		}
	}

	private double getStorage(String environmentId, String datestart,
			String dateend, ITransaction transaction) throws PortalException {

		try {
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection
					.prepareStatement("select MAX(storage) storage from saas_consumerClient where environmentId=? AND datefull BETWEEN ?  AND ?");
			try {
				statement.setString(1, environmentId);
				statement.setString(2, datestart);
				statement.setString(3, dateend);
				ResultSet resultSet = statement.executeQuery();
				try {
					if (resultSet.next()) {

						return resultSet.getDouble("storage");
					}

					return 0;
				} finally {
					resultSet.close();
				}

			} finally {
				statement.close();
			}
		} catch (PortalException e) {
			throw e;
		} catch (Exception e) {
			throw new UnexpectedException(e);
		}
	}

	private double getTransfer(String environmentId, String datestart,
			String dateend, ITransaction transaction) throws PortalException {

		try {
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection
					.prepareStatement("select SUM(transf) transf from saas_consumerClient where environmentId=? AND datefull BETWEEN ?  AND ?");
			try {
				statement.setString(1, environmentId);
				statement.setString(2, datestart);
				statement.setString(3, dateend);
				ResultSet resultSet = statement.executeQuery();
				try {
					if (resultSet.next()) {

						return resultSet.getDouble("transf");
					}

				} finally {
					resultSet.close();
				}

			} finally {
				statement.close();
			}
			return 0;
		} catch (PortalException e) {
			throw e;
		} catch (Exception e) {
			throw new UnexpectedException(e);
		}
	}
	
	private void updatePageView(Environment environment,ITransaction transaction) throws PortalException{
		ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
		Connection connection = daoTransactionJdbc.getConnection();
		PreparedStatement statement;
		
		logger.info("===> atualizando pageviews ");
		try {
			statement = connection.prepareStatement("UPDATE saas_consumerClient set pageview =? where datefull= ? and environmentId = ? ");
		
			List<ConsumerClient> clients = environment.getConsumerClients();
			
			try{					
				
				statement.setDouble(1, clients.get(0).getPageviews());				
				statement.setDate(2, clients.get(0).getDatefull());
				statement.setString(3, environment.getId());
				
				statement.execute();
			}
			finally{
				statement.close();
			}
	
		} catch (SQLException e) {		
			throw new UnexpectedException(e);
		}
	}
	
	
	private void updateTransf(Environment environment,ITransaction transaction) throws PortalException{
		ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
		Connection connection = daoTransactionJdbc.getConnection();
		PreparedStatement statement;
		
		logger.info("===> atualizando transferencia ");
		try {
			statement = connection.prepareStatement("UPDATE saas_consumerClient set transf =? where datefull= ? and environmentId = ?");
		
			List<ConsumerClient> clients = environment.getConsumerClients();
			
			try{					
				
				
				
				statement.setDouble(1, clients.get(0).getTransfer());				
				statement.setDate(2,clients.get(0).getDatefull());
				statement.setString(3, environment.getId());
				statement.execute();
			}
			finally{
				statement.close();
			}
	
		} catch (SQLException e) {		
			throw new UnexpectedException(e);
		}
	}
	
	
	private void updateStorage(Environment environment,ITransaction transaction) throws PortalException{
		ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
		Connection connection = daoTransactionJdbc.getConnection();
		PreparedStatement statement;
		logger.info("===> atualizando storage ");
		
		try {
			statement = connection.prepareStatement("UPDATE saas_consumerClient set storage =? where datefull= ? and environmentId = ?");
		
			List<ConsumerClient> clients = environment.getConsumerClients();
			
			try{					
				
				statement.setDouble(1, clients.get(0).getStorage());				
				statement.setDate(2, clients.get(0).getDatefull());
				statement.setString(3, environment.getId());
				
				statement.execute();
			}
			finally{
				statement.close();
			}
	
		} catch (SQLException e) {		
			throw new UnexpectedException(e);
		}
	}
}
